
/* ************************************************************************************* 
 * CREATED BY:          MYGENERATION
 * CREATED DATE:        1/9/2009
 * PROJECT:             MCWEBSITE
 * PURPOSE:             websiteProductDAL class
 * MODIFIED BY:         None
 * MODIFIED DATE:       None
 * MODIFIED REASON:     None
 * NOTES:               None
 **************************************************************************************/

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Web.Configuration;
using MCWEBSITE.ENTITY;
using System.Data;

namespace MCWEBSITE.DAL
{
    public class websiteProductDAL
    {
        #region  Properties

        public static string FIELD_ITEMID = "ItemID";
        public static string FIELD_LANGUAGECODE = "LanguageCode";
        public static string FIELD_ITEMNAME = "ItemName";
        public static string FIELD_ITEMCODE = "ItemCode";
        public static string FIELD_CATEGORYID = "CategoryID";
        public static string FIELD_TYPEID = "TypeID";
        public static string FIELD_DESCRIPTION = "Description";
        public static string FIELD_SALE = "Sale";
        public static string FIELD_SALEDISCOUNT = "SaleDiscount";
        public static string FIELD_ISACTIVE = "IsActive";
        public static string FIELD_IMAGE = "Image";
        public static string FIELD_ATRIBUTE1 = "Atribute1";
        public static string FIELD_ATRIBUTE2 = "Atribute2";
        public static string FIELD_ATRIBUTE3 = "Atribute3";
        public static string FIELD_ATRIBUTE4 = "Atribute4";
        public static string FIELD_ATRIBUTE5 = "Atribute5";
        public static string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ContentConnectionString"].ToString();

        #endregion

        #region Protected methods

        /// <summary>
        /// Select entity of websiteProduct
        /// </summary>
        /// <param name="ItemID">Primary Keys</param>
        /// <returns>Collection of websiteProductInfo</returns>
        public static websiteProductInfo SelectOne(int ItemID)
        {
            websiteProductInfo _websiteProductInfo = new websiteProductInfo();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectOne", ItemID);
                if (dr.Read())
                    setwebsiteProductInfo(dr, ref _websiteProductInfo);
                return _websiteProductInfo;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dr.Close();
            }
        }

        /// <summary>
        /// Select all websiteProduct
        /// </summary>
        /// <returns>Collection of websiteProductInfo</returns>
        public static List<websiteProductInfo> SelectAll()
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectAll");
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static List<websiteProductInfo> SelectByLanguage(string status, string LanguageCode)
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectByLanguage", status, LanguageCode);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static DataSet SelectByLanguageDS(string status, string LanguageCode)
        {
            try
            {
                DataSet ds = new DataSet();
                ds = SqlHelper.ExecuteDataset(ConnectionString, "sp_mc_websiteProductSelectByLanguage", status, LanguageCode);
                return ds;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public static List<websiteProductInfo> GetByLanguageAndCategoryDS(int CatId, string status, string LanguageCode)
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectByLanguageAndCategory", CatId, status, LanguageCode);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static List<websiteProductInfo> GetByLanguageAndCategory(int CatId, string status, string LanguageCode) {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteAllProductSelectByLanguageAndCategory", CatId, status, LanguageCode);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static List<websiteProductInfo> GetByLanguageAndTypeDS(int TypeID, string status, string LanguageCode) {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectByLanguageAndType", TypeID, status, LanguageCode);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static DataSet GetDetails(int ID)
        {
            try
            {
                DataSet ds = new DataSet();
                ds = SqlHelper.ExecuteDataset(ConnectionString, "sp_mc_websiteProductSelectOne", ID);
                return ds;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public static List<websiteProductInfo> SelectNtoMByLanguageAndCat(int N, int M, string status, string LanguageCode, int CatId)
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectNtoMbyLanguageAndCatID", N, M, status, LanguageCode, CatId);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static List<websiteProductInfo> SelectNtoMByLanguage(int N, int M, string status, string LanguageCode)
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectNtoMbyLanguage", N, M, status, LanguageCode);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static List<websiteProductInfo> GetByStatus(string status)
        {
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectByStatus", status);
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        /// <summary>
        /// Select
        /// </summary>
        /// <param name="websiteProductnfo">Modify this method adapting your need</param>
        /// <returns>Collection of websiteProductInfo</returns>
        public static List<websiteProductInfo> SelectFilters()
        {
            string criterias = string.Empty;
            List<websiteProductInfo> lstwebsiteProduct = new List<websiteProductInfo>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(ConnectionString, "sp_mc_websiteProductSelectByFilters");
                setListwebsiteProductInfo(ref dr, ref lstwebsiteProduct);
                return lstwebsiteProduct;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        /// <summary>
        /// Insert websiteProduct
        /// </summary>
        /// <param name="websiteProductInfo">Entity of websiteProduct</param>
        /// <returns>error code</returns>
        public static int Insert(websiteProductInfo _websiteProductInfo)
        {
            try
            {
                return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "sp_mc_websiteProductInsert"
                                            , _websiteProductInfo.LanguageCode
                                            , _websiteProductInfo.ItemName
                                            , _websiteProductInfo.ItemCode
                                            , _websiteProductInfo.CategoryID
                                            , _websiteProductInfo.TypeID                                            
                                            , _websiteProductInfo.Description
                                            , _websiteProductInfo.Sale
                                            , _websiteProductInfo.SaleDiscount
                                            //, _websiteProductInfo.Image
                                            //, _websiteProductInfo.Atribute1
                                            //, _websiteProductInfo.Atribute2
                                            //, _websiteProductInfo.Atribute5
                                            ));
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// Update websiteProduct
        /// </summary>
        /// <param name="websiteProductInfo">Entity of websiteProduct</param>
        /// <returns>error code</returns>
        public static int Update(websiteProductInfo _websiteProductInfo)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, "sp_mc_websiteProductUpdate"
                                            , _websiteProductInfo.ItemID
                                            , _websiteProductInfo.LanguageCode
                                            , _websiteProductInfo.ItemName
                                            , _websiteProductInfo.ItemCode
                                            , _websiteProductInfo.CategoryID
                                            , _websiteProductInfo.TypeID
                                            , _websiteProductInfo.Description
                                            , _websiteProductInfo.Sale
                                            , _websiteProductInfo.IsActive
                                            , _websiteProductInfo.Atribute1
                                            );
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// Delete SchoolAdmin
        /// </summary>
        /// <param name="ItemID">Primary Key</param>
        /// <returns>error code</returns>
        public static int Delete(int ItemID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, "sp_mc_websiteProductDelete", ItemID);
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int publish(int ItemID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, "sp_mc_websiteProductPublish", ItemID);
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int unpublish(int ItemID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, "sp_mc_websiteProductUnPublish", ItemID);
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int InsertResource(int NewsID, string Resources)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString, "sp_mc_websiteProductInsertResource", NewsID, Resources);
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
        #endregion

        #region "Utilities method"

        private static void setListwebsiteProductInfo(ref SqlDataReader dr, ref List<websiteProductInfo> lstwebsiteProduct)
        {
            websiteProductInfo _websiteProductInfo = null;
            while (dr.Read())
            {
                _websiteProductInfo = new websiteProductInfo();
                setwebsiteProductInfo(dr, ref _websiteProductInfo);
                lstwebsiteProduct.Add(_websiteProductInfo);
            }
        }

        private static void setwebsiteProductInfo(SqlDataReader dr, ref websiteProductInfo _websiteProductInfo)
        {
            _websiteProductInfo.ItemID = dr[websiteProductDAL.FIELD_ITEMID] != DBNull.Value ? Convert.ToInt32(dr[websiteProductDAL.FIELD_ITEMID]) : 0;
            _websiteProductInfo.LanguageCode = dr[websiteProductDAL.FIELD_LANGUAGECODE] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_LANGUAGECODE]) : string.Empty;
            _websiteProductInfo.ItemName = dr[websiteProductDAL.FIELD_ITEMNAME] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ITEMNAME]) : string.Empty;
            _websiteProductInfo.ItemCode = dr[websiteProductDAL.FIELD_ITEMCODE] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ITEMCODE]) : string.Empty;
            _websiteProductInfo.CategoryID = dr[websiteProductDAL.FIELD_CATEGORYID] != DBNull.Value ? Convert.ToInt32(dr[websiteProductDAL.FIELD_CATEGORYID]) : 0;
            _websiteProductInfo.TypeID = dr[websiteProductDAL.FIELD_TYPEID] != DBNull.Value ? Convert.ToInt32(dr[websiteProductDAL.FIELD_TYPEID]) : 0;
            _websiteProductInfo.Description = dr[websiteProductDAL.FIELD_DESCRIPTION] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_DESCRIPTION]) : string.Empty;
            _websiteProductInfo.Sale = dr[websiteProductDAL.FIELD_SALE] != DBNull.Value ? Convert.ToInt32(dr[websiteProductDAL.FIELD_SALE]) : 0;
            _websiteProductInfo.SaleDiscount = dr[websiteProductDAL.FIELD_SALEDISCOUNT] != DBNull.Value ? Convert.ToInt32(dr[websiteProductDAL.FIELD_SALEDISCOUNT]) : 0;
            _websiteProductInfo.IsActive = dr[websiteProductDAL.FIELD_ISACTIVE] != DBNull.Value ? Convert.ToBoolean(dr[websiteProductDAL.FIELD_ISACTIVE]) : false;
            _websiteProductInfo.Image = dr[websiteProductDAL.FIELD_IMAGE] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_IMAGE]) : string.Empty;
            _websiteProductInfo.Atribute1 = dr[websiteProductDAL.FIELD_ATRIBUTE1] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ATRIBUTE1]) : string.Empty;
            _websiteProductInfo.Atribute2 = dr[websiteProductDAL.FIELD_ATRIBUTE2] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ATRIBUTE2]) : string.Empty;
            _websiteProductInfo.Atribute3 = dr[websiteProductDAL.FIELD_ATRIBUTE3] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ATRIBUTE3]) : string.Empty;
            _websiteProductInfo.Atribute4 = dr[websiteProductDAL.FIELD_ATRIBUTE4] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ATRIBUTE4]) : string.Empty;
            _websiteProductInfo.Atribute5 = dr[websiteProductDAL.FIELD_ATRIBUTE5] != DBNull.Value ? Convert.ToString(dr[websiteProductDAL.FIELD_ATRIBUTE5]) : string.Empty;

        }
        #endregion Methods

        #region Private Members

        private static string TABLE_NAME = "[mc_websiteProduct]";

        #endregion
    }
}

